<?php 
$city_found = 0;

$sql1 = "SELECT `Index` FROM `CrimeRateUS` WHERE Year = '2005'";

//$sql2 = "SELECT `Index` FROM `CrimeRateCA` WHERE Year = '2005'";
$sql2 = "SELECT `Index`, `Population` FROM `CrimeRateALLStates` WHERE Year = '2005' and State = '$state'  ";


$full_state_query = "SELECT `State` FROM `StateAbbrev` WHERE Abbr = '$state'";

$resultstatename = mysql_query($full_state_query,$db);
if ($resultstatename)
{
	$row = mysql_fetch_array($resultstatename,MYSQL_ASSOC);
	if($row)
	{

		$full_state_name = $row['State'];
	}
}

$sql3 = "SELECT * FROM CrimeCities2005 WHERE `City` = '$city' and `State` =  '$state' ";

$sql4 = "SELECT * FROM CrimeCities2005 WHERE `City` = '$city' and `State` =  '$full_state_name' ";


$result1 = mysql_query($sql1,$db);
if ($result1)
{
	$row = mysql_fetch_array($result1,MYSQL_ASSOC);
	if($row)
	{

		$US_Index = $row['Index'];
	}
}


$result2 = mysql_query($sql2,$db);
if ($result2)
{
	while ($row = mysql_fetch_array($result2,MYSQL_ASSOC))
	{
		$CA_Index = $row['Index'];
		$CA_Popl = $row['Population'];
		
		$CA_Index = (100000*$CA_Index)/$CA_Popl;

		$CA_Index = number_format($CA_Index, 0, '', '');

	}
}

$result3 = mysql_query($sql3,$db);
if ($result3)
{

	while ($row = mysql_fetch_array($result3,MYSQL_ASSOC))
	{
		$city_found = 1;

		$City_Index = $row['CrimeRate'];
		$City_Population = $row['Population'];
		$City_Murder = $row['Murder and nonnegligent manslaughter'];
		$City_Rape = $row['Forcible rape'];
		$City_Robbery = $row['Robbery'];
		$City_Burglary = $row['Burglary'];
		$City_Larceny = $row['Larceny-theft'];
		$City_AutoTheft = $row['Motor vehicle theft'];
		$City_Arson = $row['Arson'];

		//echo $City_Index;


	}
}

if (!$city_found)
{
$result4 = mysql_query($sql4,$db);
if ($result4)
{

	while ($row = mysql_fetch_array($result4,MYSQL_ASSOC))
	{
		$City_Index = $row['CrimeRate'];
		$City_Population = $row['Population'];
		$City_Murder = $row['Murder and nonnegligent manslaughter'];
		$City_Rape = $row['Forcible rape'];
		$City_Robbery = $row['Robbery'];
		$City_Burglary = $row['Burglary'];
		$City_Larceny = $row['Larceny-theft'];
		$City_AutoTheft = $row['Motor vehicle theft'];
		$City_Arson = $row['Arson'];
		


	}
}

}



//$lessthen_5_percet = number_format((100*$lessthen_5)/$total_pop,2);


echo "<Table width=240px cellspacing='-1' bordar=0>";
      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td style='width:100%; font-weight:bold'>2005 Crimes Per 100,000 Inhabitants";
	  echo "</td>";
	  echo "</tr>";

	  echo "<tr style='width:100%;font-size:12px'>";
	  echo "<td >US :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$US_Index;
	  echo "</td>";
	  echo "</tr>";

      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td >".$state;
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$CA_Index;
	  echo "</td>";
	  echo "</tr>";

	  echo "<tr style='width:100%;font-size:12px'>";
	  echo "<td >".$city;
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Index;
	  echo "</td>";
	  echo "</tr>";

      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td style='width:100%; font-weight:bold'>2005 Statistics For " .$city;
	  echo "</td>";
	  echo "</tr>";

	  echo "<tr style='width:100%;font-size:12px'>";
	  echo "<td >Population :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Population;
	  echo "</td>";
	  echo "</tr>";


      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td >Murders and manslaughters :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Murder;
	  echo "</td>";
	  echo "</tr>";

	  echo "<tr style='width:100%;font-size:12px'>";
	  echo "<td >Rapes :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Rape;
	  echo "</td>";
	  echo "</tr>";
	  
      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td >Robberies :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Robbery;
	  echo "</td>";
	  echo "</tr>";
	  
	  echo "<tr style='width:100%;font-size:12px'>";
	  echo "<td >Burglaries :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Burglary;
	  echo "</td>";
	  echo "</tr>";
	  
      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td >Larceny Thefts :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Larceny;
	  echo "</td>";
	  echo "</tr>";
	  
	  echo "<tr style='width:100%;font-size:12px'>";
	  echo "<td >Motor Vehicle Thefts :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_AutoTheft;
	  echo "</td>";
	  echo "</tr>";
	  
      echo "<tr style='width:100%; background:#F5DEB3; font-size:12px'>";
	  echo "<td >Arsons :";
	  echo "</td>";
	  echo "<td align='right' nowrap='nowrap'>".$City_Arson;
	  echo "</td>";
	  echo "</tr>";
echo "</Table>";
?>